package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.salesmanage.util.SaleOrder;
import com.turing.post.salesmanage.util.SaleOrderRealNameDetail;
import com.turing.post.salesmanage.util.SaleOrderRealNameDetailDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 23, 2012
 */
@Stateless(mappedName = "salesmanage.impl.SaleOrderRealNameDetailDaoImpl")
@Remote
@Local
public class SaleOrderRealNameDetailDaoImpl implements
		SaleOrderRealNameDetailDao {

	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public SaleOrderRealNameDetail getSaleOrderRealNameDetailByPrimaryKey(
			SaleOrderRealNameDetail saleOrderRealNameDetail) {
		return _em.find(SaleOrderRealNameDetail.class, saleOrderRealNameDetail);
	}

	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, BigInteger> getSaleOrderRealNameDetailList(
			BigInteger saleOrderId) {
		String sql = "select * from SaleOrderRealNameDetail t where t.saleorderid="
				+ saleOrderId;
		Query query = _em.createNativeQuery(sql, SaleOrderRealNameDetail.class);
		List<SaleOrderRealNameDetail> list = query.getResultList();
		HashMap<String, BigInteger> map = new HashMap<String, BigInteger>();
		SaleOrderRealNameDetail sornd = null;
		for (int i = 0; i < list.size(); i++) {
			sornd = list.get(i);
			map.put(sornd.getCardSeqNo(), sornd.getCustomId());
		}
		return map;
	}

	/**
	 * 根据销售单编号查询该销售单持卡人实名明细
	 * 
	 * @param saleOrderId
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<SaleOrderRealNameDetail> getAllSaleOrderRealNameDetailList(
			BigInteger saleOrderId) {
		String sql = "select * from SaleOrderRealNameDetail t where t.saleorderid= ?1";
		Query query = _em.createNativeQuery(sql, SaleOrderRealNameDetail.class);
		query.setParameter(1, saleOrderId);
		return query.getResultList();
	}

	// 销售单号+批次号+卡号+卡顺序号范围+证件号+姓名模糊查询

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrderRealNameDetail> getSaleOrderRealNameDetailListByCondition(
			BigInteger saleOrderId, String batchNo, String cardNo,
			String startCardSeqNo, String endCardSeqNo, String name,
			String idNo, Integer page) {
		String sql = "select s.* from SaleOrderRealNameDetail s,PersonalCustom p where s.customid=p.personalid and s.SaleOrderId=?1 and "
				+ "s.batchno like ?2 and s.cardno like ?3 and (?4 is null or s.cardseqno>=to_number(?4)) "
				+ "and (?5 is null or s.cardseqno<=to_number(?5)) and p.name like ?6 and p.idno like ?7 order by s.batchNo,s.cardSeqNo";

		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		Query query = _em.createNativeQuery(sql, SaleOrderRealNameDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, saleOrderId);
		if (PCPSUtil.isEmpty(batchNo)) {
			batchNo = "";
		}
		query.setParameter(2, "%" + batchNo + "%");
		if (PCPSUtil.isEmpty(cardNo)) {
			cardNo = "";
		}
		query.setParameter(3, "%" + cardNo + "%");
		query.setParameter(4, startCardSeqNo);
		query.setParameter(5, endCardSeqNo);
		if (PCPSUtil.isEmpty(name)) {
			name = "";
		}
		query.setParameter(6, "%" + name + "%");
		if (PCPSUtil.isEmpty(idNo)) {
			idNo = "";
		}
		query.setParameter(7, "%" + idNo + "%");
		return query.getResultList();
	}

	@Override
	public BigInteger getSaleOrderRealNameDetailTotalByCondition(
			BigInteger saleOrderId, String batchNo, String cardNo,
			String startCardSeqNo, String endCardSeqNo, String name, String idNo) {
		String sql = "select nvl(count(*),0) count from SaleOrderRealNameDetail s,PersonalCustom p where s.customid=p.personalid and s.SaleOrderId=?1 and "
				+ "s.batchno like ?2 and s.cardno like ?3 and (?4 is null or s.cardseqno>=to_number(?4)) "
				+ "and (?5 is null or s.cardseqno<=to_number(?5)) and p.name like ?6 and p.idno like ?7 order by s.batchNo,s.cardSeqNo";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, saleOrderId);
		if (PCPSUtil.isEmpty(batchNo)) {
			batchNo = "";
		}
		query.setParameter(2, "%" + batchNo + "%");
		if (PCPSUtil.isEmpty(cardNo)) {
			cardNo = "";
		}
		query.setParameter(3, "%" + cardNo + "%");
		query.setParameter(4, startCardSeqNo);
		query.setParameter(5, endCardSeqNo);
		if (PCPSUtil.isEmpty(name)) {
			name = "";
		}
		query.setParameter(6, "%" + name + "%");
		if (PCPSUtil.isEmpty(idNo)) {
			idNo = "";
		}
		query.setParameter(7, "%" + idNo + "%");
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public void insertSaleOrderRealNameDetail(
			SaleOrderRealNameDetail saleOrderRealNameDetail)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, saleOrderRealNameDetail);
	}

	@Override
	public void updateSaleOrderRealNameDetail(
			SaleOrderRealNameDetail saleOrderRealNameDetail)
			throws PCPSApplicationException {
		GeneralDao.update(_em, saleOrderRealNameDetail);
	}

	@Override
	public Integer deleteSaleOrderRealNameDetail(
			SaleOrderRealNameDetail saleOrderRealNameDetail)
			throws PCPSApplicationException {
		try {
			String sql = "delete from SaleOrderRealNameDetail where saleOrderId="
					+ saleOrderRealNameDetail.getSaleOrderId()
					+ " and BatchNo='"
					+ saleOrderRealNameDetail.getBatchNo()
					+ "' and CardSeqNo='"
					+ saleOrderRealNameDetail.getCardSeqNo() + "'";
			Query query = _em.createNativeQuery(sql);
			return query.executeUpdate();
		} catch (Exception e) {
			return -1;
		}
	}

	@Override
	public SaleOrderRealNameDetail getSaleOrderRealNameByCondition(
			BigInteger saleOrderId, String batchNo, String cardSeqNo) {
		SaleOrderRealNameDetail sornd = new SaleOrderRealNameDetail();
		sornd.setSaleOrderId(saleOrderId);
		sornd.setBatchNo(batchNo);
		sornd.setCardSeqNo(cardSeqNo);
		return _em.find(SaleOrderRealNameDetail.class, sornd);
	}

	@Override
	public Integer deleteSaleOrderRealNameDetailByOrderId(BigInteger saleOrderId)
			throws PCPSApplicationException {
		try {
			String sql = "delete from SaleOrderRealNameDetail where saleOrderId=?1";
			Query query = _em.createNativeQuery(sql).setParameter(1,
					saleOrderId);
			return query.executeUpdate();
		} catch (Exception e) {
			return -1;
		}
	}

	/**
	 * 根据两要素检索持卡人明细
	 * 
	 * @param Name
	 *            姓名
	 * @param CardNo
	 *            卡号
	 * @return 检索清单
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getPersonalCustomCardDatil(PCPSWebInput webInput,
			String Pername, String CardNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo()))
			return null;
		String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
		String sql = "select b.Name,b.Email,b.LinkTel,a.cardno,c.cardclassicname,a.Balance "
				+ " from ActivateCard a, PersonalCustom b, cardclassic c"
				+ " where a.ownerid = b.personalid"
				+ " and a.cardclassicid = c.cardclassicid"
				+ " and (?1 is null or b.Name like '%'||?1||'%')"
				+ " and (?2 is null or a.cardno = ?2)"
				+ " and (?3 is null or b.issuecompanyno=?3)";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, Pername);
		query.setParameter(2, CardNo);
		query.setParameter(3, issueCompanyNo);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleOrderRealNameDetailByAlarmRule2(
			String issueCompanyNo, String startTime, String endTime) {
		if (PCPSUtil.isEmpty(issueCompanyNo) || PCPSUtil.isEmpty(startTime)
				|| PCPSUtil.isEmpty(endTime))
			return new ArrayList<Object[]>();
			
		String sql = " select a.saleorderid,a.customid,sum(c.initdeposit),d.name,b.salebranchno,to_char(b.trandate,'yyyy-mm-dd') " +
				" from SaleOrderRealNameDetail a,SaleOrder b,SaleOrderCardDetail c,PersonalCustom d " +
				" where a.saleorderid = b.saleorderid " +
				" and b.saleorderid = c.saleorderid " +
				" and a.customid = d.personalid  " +
				" and substr(b.salebranchno,0,6) = ?1 " +
				" and b.trandate >= to_date(?2,'yyyy-mm-dd') " +
				" and b.trandate <= to_date(?3,'yyyy-mm-dd') " +
				" and c.startcardseqno <= a.cardseqno " +
				" and c.endcardseqno >= a.cardseqno " +
				" and b.orderstatus <> ?4 " +
				" group by a.saleorderid,a.customid,d.name,b.salebranchno,b.trandate " +
				" order by a.saleorderid asc ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		query.setParameter(4, SaleOrder.SALEORDER_ORDERSTATUS_CANCEL);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}
}
